IF OBJECT_ID('fSegmentIsChildOf') IS NOT NULL 
BEGIN 
    DROP FUNCTION fSegmentIsChildOf
    IF OBJECT_ID('fSegmentIsChildOf') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION fSegmentIsChildOf >>>'
    ELSE 
        PRINT '<<< DROPPED FUNCTION fSegmentIsChildOf >>>'
END
GO
CREATE FUNCTION dbo.fSegmentIsChildOf
	(
        @SegmentId   int          = NULL
    ,   @SegmentName varchar(255) = NULL
	)
RETURNS bit
AS
BEGIN
    DECLARE
        @bit             bit
    ,   @ParentSegmentId int
    ,   @name            varchar(255)
    ,   @counter         int

    SET @bit = 0
    SET @counter = 0

    SELECT
        @ParentSegmentId = SegSearch.ParentSegmentId
    ,   @name        = (SELECT SegmentName FROM Segment WHERE SegmentId = SegSearch.ParentSegmentId)
    FROM
        Segment AS SegSearch
    WHERE
        SegSearch.SegmentId = @SegmentId

    WHILE (@ParentSegmentId IS NOT NULL) AND (@counter < 500)
    BEGIN
        SET @counter = @counter + 1
        IF @name = @SegmentName
        BEGIN
            SET @bit = 1
        END
        SET @SegmentId = @ParentSegmentId
        SELECT
            @ParentSegmentId = SegSearch.ParentSegmentId
        ,   @name            = (SELECT SegmentName FROM Segment WHERE SegmentId = SegSearch.ParentSegmentId)
        FROM
            Segment AS SegSearch
        WHERE
            SegSearch.SegmentId = @SegmentId
    END    
RETURN @bit
END
GO
GRANT EXECUTE ON fSegmentIsChildOf TO public
GO
IF OBJECT_ID('fSegmentIsChildOf') IS NOT NULL 
    PRINT '<<< CREATED FUNCTION fSegmentIsChildOf >>>' 
ELSE 
    PRINT '<<< FAILED CREATING FUNCTION fSegmentIsChildOf >>>' 
GO 